package com.createidea.scrumfriend.dao.story;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.createidea.scrumfriend.dao.BaseDaoImpl;
import com.createidea.scrumfriend.to.ProjectTo;
import com.createidea.scrumfriend.to.SprintTo;
import com.createidea.scrumfriend.to.StoryTo;

public class StoryDaoImpl extends BaseDaoImpl implements  StoryDao {

	@Override
	public List<StoryTo> storysByStatus(int status) {
		// TODO Auto-generated method stub
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(StoryTo.class);
		detachedCriteria.add(Restrictions.eq("status", status));
		detachedCriteria.addOrder(Order.desc("priority"));
		return this.getHibernateTemplate().findByCriteria(detachedCriteria);
	}
	
	@Override
	public void updateStory(StoryTo story) {
		// TODO Auto-generated method stub
		this.getHibernateTemplate().saveOrUpdate(story);
	}

	@Override
	public StoryTo getStoryById(String card_id) {
		// TODO Auto-generated method stub
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(StoryTo.class);
		detachedCriteria.add(Restrictions.eq("id", card_id));
		return (StoryTo)this.getHibernateTemplate().findByCriteria(detachedCriteria).get(0);
	}

	@Override
	public List<StoryTo> getStoriesForProject(String projectId) {
		// TODO Auto-generated method stub
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(StoryTo.class);
		detachedCriteria.add(Restrictions.eq("project.id", projectId));
		return this.getHibernateTemplate().findByCriteria(detachedCriteria);
	}

	@Override
	public void createStory(StoryTo story) {
		// TODO Auto-generated method stub
		this.getHibernateTemplate().save(story);
	}

	@Override
	public List<StoryTo> getStoriesForProjectByStatus(String projectId, int status) {
		// TODO Auto-generated method stub
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(StoryTo.class);
		detachedCriteria.add(Restrictions.eq("project.id", projectId));
		detachedCriteria.add(Restrictions.eq("status", status));
		detachedCriteria.addOrder(Order.desc("priority"));
		return this.getHibernateTemplate().findByCriteria(detachedCriteria);
		
	}

	@Override
	public float calculateTotalPointsForProject(ProjectTo project) {
		// TODO Auto-generated method stub
         final String sql="select sum(point) as point from story where project_id='"+project.getId()+"'";
		 return calculateStoryPoint(sql);
	}

	@Override
	public float calculateCompletedPointForProject(ProjectTo project) {
		// TODO Auto-generated method stub
		final String sql="select sum(point) as point from story where status=3 and project_id='"+project.getId()+"'";
		return calculateStoryPoint(sql);
	}

	@Override
	public float calculateRemainingPointForProject(ProjectTo project) {
		// TODO Auto-generated method stub
		return calculateTotalPointsForProject(project)-calculateCompletedPointForProject(project);
	}
	

	@Override
	public float calculateCommittedStoryPoint(String sprintId) {
		// TODO Auto-generated method stub
		final String sql="select sum(point) as point from story where sprint_id='"+sprintId+"'";
		return calculateStoryPoint(sql);
	}

	@Override
	public float calculateCompletedStoryPoint(String sprintId) {
		// TODO Auto-generated method stub
		final String sql="select sum(point) as point from story where status=3 and sprint_id='"+sprintId+"'";
		return calculateStoryPoint(sql);
	}
	
	public float calculateStoryPoint(final String sql) {
		return (Float) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Float doInHibernate(Session session) throws HibernateException, SQLException {
						SQLQuery query = session.createSQLQuery(sql).addScalar("point", Hibernate.FLOAT);
						Object count = query.uniqueResult();
						if(count==null)
							return Float.valueOf(0);
						else
						    return (Float) count;
					}
				});
	}

	@Override
	public List<StoryTo> getStoriesForSprintByStatus(String sprintId, int status) {
		// TODO Auto-generated method stub
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(StoryTo.class);
		detachedCriteria.add(Restrictions.eq("sprint.id", sprintId));
		detachedCriteria.add(Restrictions.eq("status", status));
		return this.getHibernateTemplate().findByCriteria(detachedCriteria);
	}

	
}
